package com.mc.dao.Impl;

import com.mc.VO.TTVO;
import com.mc.bean.TT;
import com.mc.dao.TTDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lyx
 * 团队老师关系数据访问实现类
 */
public class TTDaoImpl extends BaseDao implements TTDao {
    private TT createTT(ResultSet rs) throws SQLException {
        TT tt = new TT(rs.getInt(1),rs.getInt(2));
        return tt;
    }

    @Override
    public List<TTVO> findByTeamIdView(int teamid) {
        List<TTVO> list = new ArrayList<>();
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select tt.`teamid`,teacher.tid,teacher.tname,teacher.tsex,teacher.tage,teacher.ttel from tt,teacher where tt.tid=teacher.tid and teamid = ?";
            super.getPst(sql);
            pstmt.setInt(1,teamid);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()){
                //创建对象
                TTVO ttvo = new TTVO(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getString(6));
                //放在集合中
                list.add(ttvo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return list;
    }

    @Override
    public List<TT> findByTid(int tid) {
        List<TT> list = new ArrayList<TT>();
        try {
            regist();
            connection();
            String sql = "select * from tt where tid = ?";
            getPst(sql);
            pstmt.setInt(1, tid);
            getRs();
            while (rs.next()) {
                TT tt = createTT(rs);
                list.add(tt);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    @Override
    public List<TT> findByTeamId(int teamid) {
        List<TT> list = new ArrayList<TT>();
        try {
            regist();
            connection();
            String sql = "select * from tt where teamid = ?";
            getPst(sql);
            pstmt.setInt(1, teamid);
            getRs();
            while (rs.next()) {
                TT tt = createTT(rs);
                list.add(tt);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    @Override
    public int add(TT tt) {
        int i = 0;
        try {
            //加载驱动
            super.regist();
            //创建连接
            super.connection();
            //创建预编译指令
            String sql = "insert into tt (teamid,tid) values (?,?)";
            super.getPst(sql);
            super.pstmt.setInt(1,tt.getTeamid());
            super.pstmt.setInt(2,tt.getTid());
            //执行预编译指令，得到结果
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭
            super.close();
        }
        return i;
    }

    @Override
    public int delete(int teamid, int tid) {
        int i = 0;
        try {
            //加载驱动
            super.regist();
            //创建连接
            super.connection();
            //创建预编译指令
            String sql = "delete from tt where teamid =? and tid = ?";
            super.getPst(sql);
            super.pstmt.setInt(1,teamid);
            super.pstmt.setInt(2,tid);
            //执行预编译指令，得到结果
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭
            super.close();
        }
        return i;
    }

    @Override
    public int update(TT tt) {
        int i = 0;
        try {
            //加载驱动
            super.regist();
            //创建连接
            super.connection();
            //创建预编译指令
            String sql = "update tt set tid=? where teamid = ?";
            super.getPst(sql);
            super.pstmt.setInt(1,tt.getTid());
            super.pstmt.setInt(2,tt.getTeamid());
            //执行预编译指令，得到结果
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭
            super.close();
        }
        return i;
    }
}
